package com.kehutong.user.service.impl;

import com.kehutong.common.DoveClient;
import com.kehutong.common.ExcelUtil;
import com.kehutong.common.constant.CommonConstant;
import com.kehutong.common.enums.CardType;
import com.kehutong.common.enums.Gender;
import com.kehutong.common.enums.HouseType;
import com.kehutong.common.permission.UserLoginInfo;
import com.kehutong.common.urlconfig.FileUrlConfig;
import com.kehutong.common.util.Token;
import com.kehutong.contact.entity.Resident;
import com.kehutong.contact.entity.ResidentHouse;
import com.kehutong.contact.enums.BindType;
import com.kehutong.contact.enums.ResidentType;
import com.kehutong.contact.service.impl.ResidentServiceImpl;
import com.kehutong.user.dao.ParkingUserDao;
import com.kehutong.user.service.ParkingUserService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.coraframework.authz.Subject;
import org.coraframework.authz.SubjectUtil;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.mvc.webservice.WebService;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.execute.Condition;
import org.coraframework.orm.jdbc.execute.PageCondition;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.EnumUtil;
import org.coraframework.util.Objects;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @author: liukunlong
 * @date: 2021-08-04 19:08
 */
@WebService("/crm/assets/parking/user/service")
public class ParkingUserServiceImpl implements ParkingUserService {
    private final static String[] EXCEL_IMPORT_PARKING_OWNER_HEADER =
            {"姓名", "手机号", "身份证", "微信号", "性别",
                    "生日", "邮箱", "地区", "职业", "描述","项目"
                    , "车场","车位", "身份"};     //车位业主表头

    private final static boolean[] EXCEL_IMPORT_PARKING_OWNER_REQUIRED = {true, true, false, false, false, false,
            false, false, false, false,true,true,true,true};     //是否必填
    private final static String [] EXCEL_IMPORT_PARKING_OWNER_ROWTEMP = {"张三（示例）", "14500000000", "430535198012080000", "88821wq", "男", "1984-06-22", "888666@163.com", "广东省-深圳市", "医生", ""
            , "物业部", "1栋","P101", "业主"};   //模板行1

    @Inject
    private ParkingUserDao parkingUserDao;

    @Inject
    private JdbcSession jdbcSession;

    @Inject
    private DoveClient doveClient;

    @Inject
    private FileUrlConfig fileUrlConfig;

    @Inject
    private ResidentServiceImpl residentServiceImpl;

    /**
     * 根据条件车户列表
     *
     * @param jsonObject 查询对象
     * @return 标签列表
     * @throws IOException 操作异常
     */
    @ReqMapping("/parking/user/page")
    public Page<Map<String, Object>> parkingUserPage(JSONObject jsonObject) throws Exception {
        //数据权限类型 1.全部业主 2.我管理的业主 3.下属管理的业主
        String dataPvType = jsonObject.getString("dataPvType");
        PageCondition query = Condition.createPage(false);
        String search = jsonObject.getString("search");
        if(Objects.nonEmpty(search)){
            query.markBegin();
            query.like("a.id", search).or();
            query.like("a.realname", search).or();
            query.like("a.mobile", search).or();
            query.markEnd();
        }
        String communityNo = jsonObject.getString("communityNo");
        if (Objects.nonEmpty(communityNo)) {
            query.eq("b.communityNo",communityNo);
        }

        String communityName = jsonObject.getString("communityName");
        if (Objects.nonEmpty(communityName)) {
            query.like("b.communityName",communityName);
        }
        //根据不同类型获取业主的数据权限
        if (Objects.nonEmpty(dataPvType)) {
            List<String> assetsIdList = buildAssetsPv(dataPvType);

            if(Objects.isEmpty(assetsIdList)){
                return new Page<>();
            }
            if(Objects.nonEmpty(assetsIdList)){
                query.in("b.houseNo",assetsIdList);
            }
        }

        query.order("a.createTime","desc");
        query.page(jsonObject);
        // 根据以上条件查询房屋的分页数据
        Page<Map<String, Object>> page = parkingUserDao.loadPage(query);
        return page;
    }

    /**
     * 根据条件获取车位业主
     *
     * @param jsonObject 查询对象
     * @return 操作结果
     * @throws Exception 操作异常
     */
    @ReqMapping("/parking/owner/page")
    public Page<ResidentHouse> parkingOwnerPage(JSONObject jsonObject) throws Exception {
        //数据权限类型 1.全部业主 2.我管理的业主 3.下属管理的业主
        String dataPvType = jsonObject.getString("dataPvType");
        List<String> bindTypeList = new ArrayList<>();
        bindTypeList.add(BindType.NO_BIND.getId());
        bindTypeList.add(BindType.BIND.getId());
        FindPage<ResidentHouse> findResidentHouse = jdbcSession.findPage(ResidentHouse.class)
                .eq("houseType",HouseType.PARKING)
                .eq("type", ResidentType.OWNER)
                .in("bindType", bindTypeList)
                .eq(jsonObject,"communityNo")
                .like(jsonObject, "communityName")
                .eq("deleted", false)
                .order("createTime", "desc");

        String search = jsonObject.getString("search");
        if(Objects.nonEmpty(search)){
            findResidentHouse.markBegin();
            findResidentHouse.like("id", search).or();
            findResidentHouse.like("realname", search).or();
            findResidentHouse.like("mobile", search).or();
            findResidentHouse.markEnd();
        }
        //根据不同类型获取业主的数据权限
        if (Objects.nonEmpty(dataPvType)) {
            List<String> assetsIdList = buildAssetsPv(dataPvType);

            if(Objects.isEmpty(assetsIdList)){
                return new Page<>();
            }

            if(Objects.nonEmpty(assetsIdList)){
                findResidentHouse.in("houseNo",assetsIdList);
            }
        }
        return findResidentHouse.page(jsonObject).exe();
    }

    private List<String> buildAssetsPv(String dataPvType) throws Exception {
        List<String> result = new ArrayList<>();
        Subject subject = SubjectUtil.getSubject();
        UserLoginInfo userLoginInfo = subject.getAttr(CommonConstant.CURRENT_USER_INFO + ":" + subject.getUserId());
        JSONObject permissionAssetsResult = doveClient.post("/basic/house/service/permissionAssetsList", http -> {
            JSONObject body = new JSONObject();
            body.put("dataPvType",dataPvType);
            body.put("userId",userLoginInfo.getUserId());
            body.put("departmentNo",userLoginInfo.getDepartmentNo());
            body.put("manageDepartmentNo",userLoginInfo.getManageDepartmentNo());
            body.put("identity",userLoginInfo.getIdentity());
            body.put("roles",userLoginInfo.getRoles());
            body.put("allData", userLoginInfo.isAllData());
            http.setBody(body.toString());
        });
        JSONObject data = permissionAssetsResult.getJSONObject("data");
        if(Objects.nonEmpty(data)){
            JSONArray houseJsonArray = data.getJSONArray("houses");
            JSONArray parkingJsonArray = data.getJSONArray("parkings");
            JSONArray shopJsonArray = data.getJSONArray("shops");
            if (Objects.nonNull(houseJsonArray)) {
                for (Object houseJsonObj : houseJsonArray) {
                    JSONObject houseJson = (JSONObject) houseJsonObj;
                    String id = houseJson.getString("id");
                    result.add(id);
                }
            }
            if (Objects.nonNull(parkingJsonArray)) {
                for (Object parkingJsonObj : parkingJsonArray) {
                    JSONObject parkingJson = (JSONObject) parkingJsonObj;
                    String id = parkingJson.getString("id");
                    result.add(id);
                }
            }
            if (Objects.nonNull(shopJsonArray)) {
                for (Object shopJsonObj : shopJsonArray) {
                    JSONObject shopJson = (JSONObject) shopJsonObj;
                    String id = shopJson.getString("id");
                    result.add(id);
                }
            }
        }
        return result;
    }

    @Override
    public Object parkingUserExport(JSONObject jsonObject) throws Exception {
        File tmpFile = null;

        Boolean isModel = jsonObject.getBoolean("isModel");
        String residentType=jsonObject.getString("residentType");  //住户类型
        String residentTypeName=ResidentType.getById(residentType).getDesc();
        String[]headers=null;  //头
        boolean[]requrieds=null; //是否必须
        String[]rowTemplateCells=null; //模板row
        if(!Objects.isNull(residentType)){
            if(ResidentType.OWNER.getId().equals(residentType)){  //业主
                headers=EXCEL_IMPORT_PARKING_OWNER_HEADER;
                requrieds=EXCEL_IMPORT_PARKING_OWNER_REQUIRED;
                rowTemplateCells=EXCEL_IMPORT_PARKING_OWNER_ROWTEMP;

            }
        }else{
            return Result.error(0, "residentType不能为空");
        }
        if(isModel){
            Workbook wb = generateWorkBook(headers,requrieds,rowTemplateCells);
            tmpFile = ExcelUtil.writeXlsFile(wb, "车位"+residentTypeName+"信息导入模板", ".xlsx");
        } else {
            jsonObject.put("pageNo",1);
            jsonObject.put("pageSize",Short.MAX_VALUE);
//            Page<Map<String, Object>> page = residentUserPage(jsonObject);
//            List<Map<String, Object>> list = page.getList();
            List<ResidentHouse>residentHouseList=getResidentByParam(jsonObject).getList();
            Workbook wb = generateWorkBookNew(residentHouseList,headers,requrieds,residentType);
            tmpFile = ExcelUtil.writeXlsFile(wb, "车位"+residentTypeName+"信息", ".xlsx");
        }
        return Result.download(tmpFile);
    }

    @Override
    public Object parkingUserCheckFile(JSONObject jsonObject) throws Exception {
        InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
        String residentType=jsonObject.getString("residentType");
        String[] headers=EXCEL_IMPORT_PARKING_OWNER_HEADER;
        if(ResidentType.OWNER.getId().equals(residentType)){  //业主
            headers=EXCEL_IMPORT_PARKING_OWNER_HEADER;

        }
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        // 3行表头 开始读
        Row firstRow = sheet.getRow(sheet.getFirstRowNum() + 2);
        List<String> errMsgs = new ArrayList<>();

        for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
            firstRow.getCell(i).setCellType(CellType.STRING);
            String s = firstRow.getCell(i).getStringCellValue();
            if (i < headers.length) {
                if (!headers[i].equals(s)) {
                    errMsgs.add("请确认【" + headers[i] + "】为第" + (i + 1) + "列");
                }
            }
        }

        JSONObject errorJson = new JSONObject();
        // 表头校验结果
        if (!errMsgs.isEmpty()) {
            errorJson.put("normalCount", 0);
            errorJson.put("errCount", 0);
            errorJson.put("successCount", 0);
            errorJson.put("errMsgs", errMsgs);
            return Result.error(0, "数据解析异常", errorJson);
        }

        Map<Integer,Integer> errCount = new HashMap<>();
        Integer size = 0;

        List<JSONObject> parkingJson = getParkingJsons(sheet,headers);
        Map<String, String> parkingMap = getParkingInfo(parkingJson);

        // 第4行开始遍历获取数据
        for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
            Row row = sheet.getRow(rIndex);

            if(Objects.isNull(row)){
                continue;
            }

            List<String> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (i < headers.length && Objects.nonNull(cell)) {
                    cell.setCellType(CellType.STRING);
                    String value = cell.getStringCellValue();
                    if(Objects.nonEmpty(value)){
                        cellValues.add(value);
                    }
                }
            }

            if(Objects.isEmpty(cellValues)){
                continue;
            }

            // 校验数据
            checkExcelData(row, (rIndex + 1), errMsgs, errCount, parkingMap,headers,residentType);

            size++;
        }

        errorJson.put("normalCount", size);
        if(size > 3000){
            errMsgs = new ArrayList<>();
            errMsgs.add("最多支持导入3000条数据");
            errorJson.put("errCount", 0);
            errorJson.put("successCount", 0);
            errorJson.put("errMsgs", errMsgs);
            return Result.error(0, "数据解析异常", errorJson);
        }

        // 数据读取结果
        if (!errMsgs.isEmpty()) {
            int errSum = errCount.keySet().size();
            errorJson.put("errMsgs", errMsgs);
            errorJson.put("errCount", errSum);
            errorJson.put("successCount", size - errSum);

            return Result.error(0, "数据解析异常", errorJson);
        }

        errorJson.put("errMsgs", errMsgs);
        errorJson.put("errCount", 0);
        errorJson.put("successCount", size);
        return Result.success("成功", errorJson);
    }

    @Override
    public Object parkingUserReadFile(Token token, JSONObject jsonObject) throws Exception {
        InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
        String residentType=jsonObject.getString("residentType");
        String[] headers=EXCEL_IMPORT_PARKING_OWNER_HEADER;
        if(ResidentType.OWNER.getId().equals(residentType)){  //业主
            headers=EXCEL_IMPORT_PARKING_OWNER_HEADER;

        }
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        List<JSONObject> residents = new ArrayList<>();

        List<JSONObject> houses = getParkingJsons(sheet,headers);
        Map<String, String> houseMap = getParkingInfo(houses);

        // 第4行开始遍历获取数据
        for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
            Row row = sheet.getRow(rIndex);

            if(Objects.isNull(row)){
                continue;
            }

            List<String> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (i < headers.length && Objects.nonNull(cell)) {
                    cell.setCellType(CellType.STRING);
                    String value = cell.getStringCellValue();
                    if(Objects.nonEmpty(value)){
                        cellValues.add(value);
                    }
                }
            }
            if(Objects.isEmpty(cellValues)){
                continue;
            }

            // 填充数据数据
            JSONObject json = getExcelData(row, houseMap,residentType);
            residents.add(json);
        }

        JSONObject json = new JSONObject();
        json.put("ownerList", residents);
        json.put("residentType",residentType);
        if(ResidentType.OWNER.getId().equals(residentType)) {  //业主
            residentServiceImpl.batchSaveOrUpdateOwner(token, json);
        }else{
            residentServiceImpl.batchSaveOrUpdateNoOwner(token, json);
        }


        return Result.success();
    }

    public XSSFWorkbook generateWorkBook(String[]headers, boolean[]requrieds, String[]rowTemplateCells) throws Exception {
        // 生成模板文件
        XSSFWorkbook wb = new XSSFWorkbook();

        // 设置头信息
        Sheet sheet = setSheetCellStyle(wb,headers,requrieds);

        int cellNum = 0;
        Row row = sheet.createRow(3);
        DataFormat format = wb.createDataFormat();
        CellStyle style= wb.createCellStyle();
        style.setDataFormat(format.getFormat("@"));
        for(String info:rowTemplateCells){
            Cell cell = row.createCell(cellNum++);
            cell.setCellStyle(style);
            cell.setCellValue(info);
        }
        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    /**
     * @author zhouweipeng
     * @param list
     * @param headers
     * @param requrieds
     * @return
     * @throws Exception
     *
     */
    public XSSFWorkbook generateWorkBookNew(List<ResidentHouse> list,String[]headers,boolean[]requrieds,String residentType) throws Exception {
        // 生成模板文件
        XSSFWorkbook wb = new XSSFWorkbook();
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        // 设置头信息
        Sheet sheet = setSheetCellStyle(wb,headers,requrieds);

        Set<String> houseNos = new HashSet<>();
        list.forEach(item->{
            String houseNo =item.getHouseNo();
            if (Objects.nonEmpty(houseNo)) {
                houseNos.add(houseNo);
            }
        });
        Map<String, JSONObject> parkingMap = getParkingMap(new ArrayList<>(houseNos));
        int cellNum = 0;
        for (int i = 0; i < list.size(); i++) {
            ResidentHouse residentHouse=list.get(i);
            Resident resident=jdbcSession.findById(Resident.class,residentHouse.getResidentNo());
            cellNum = 0;
            Row row = sheet.createRow(i + 3);
            DataFormat format = wb.createDataFormat();
            CellStyle style= wb.createCellStyle();
            style.setDataFormat(format.getFormat("@"));
            row.createCell(cellNum++).setCellValue(residentHouse.getRealname());
            row.createCell(cellNum++).setCellValue(residentHouse.getMobile());
            row.createCell(cellNum++).setCellValue(resident.getCardNo());
            row.createCell(cellNum++).setCellValue(resident.getWeixinNo());

            if (Objects.nonNull(resident.getGender())){
                String genderStr = resident.getGender().getDesc();
                row.createCell(cellNum++).setCellValue(genderStr);
            }else{
                row.createCell(cellNum++).setCellValue("");
            }

            LocalDateTime birthdayTime =resident.getBirthDay();
            if(birthdayTime!=null){
                row.createCell(cellNum++).setCellValue(df.format(birthdayTime));
            }else{
                row.createCell(cellNum++).setCellValue("");
            }
            row.createCell(cellNum++).setCellValue(resident.getEmail());
            row.createCell(cellNum++).setCellValue(resident.getAddress());
            row.createCell(cellNum++).setCellValue(resident.getOfferName());
            row.createCell(cellNum++).setCellValue(resident.getRemark());
            String houseNo = residentHouse.getHouseNo();
            Map<String, Object> parking = parkingMap.get(houseNo);
            if(Objects.nonEmpty(parking)){
                row.createCell(cellNum++).setCellValue(String.valueOf(parking.get("communityName")));
                row.createCell(cellNum++).setCellValue(String.valueOf(parking.get("yardName")));
                row.createCell(cellNum++).setCellValue(String.valueOf(parking.get("name")));
                row.createCell(cellNum++).setCellValue(residentHouse.getType().getDesc());
            }
            if(ResidentType.FRIEND.getId().equals(residentType)){
                row.createCell(cellNum++).setCellValue(residentHouse.getRelationType().getDesc());
            }else if(ResidentType.LESSEE.getId().equals(residentType)){
                row.createCell(cellNum++).setCellValue(df.format(residentHouse.getStartTime()));
                row.createCell(cellNum++).setCellValue(df.format(residentHouse.getEndTime()));
            }
        }
        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }
    public Map<String, String> getParkingInfo(List<JSONObject> jsons) throws Exception {

        JSONObject result = doveClient.post("/basic/parking/service/getParkingInfo", http->{
            JSONObject body = new JSONObject();
            body.put("list", jsons);
            http.setBody(body.toJSONString());
        });

        JSONArray data = result.getJSONArray("data");

        if (Objects.isNull(data)) {
            return new HashMap<>();
        }

        Map<String,String> houseMap = new HashMap<>();
        data.forEach(item->{
            JSONObject obj = (JSONObject) item;
            houseMap.put(obj.getString("info"), obj.getString("id"));
        });

        return houseMap;
    }


    private Sheet setSheetCellStyle(XSSFWorkbook wb,String[]headers,boolean[]requrieds) {
        //设置字体颜色+文字水平居中
        CellStyle fontColorCellStyle = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        XSSFColor color = new XSSFColor(new java.awt.Color(255, 0, 0));
        font.setColor(color);
        fontColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
        fontColorCellStyle.setFont(font);

        // 允许文字换行
        XSSFFont fontStyle = wb.createFont(); // 字体样式
//		fontStyle.setBold(true);// 加粗
        fontStyle.setFontName("宋体");
        fontStyle.setFontHeightInPoints((short) headers.length); // 大小
        CellStyle noticeCellStyle = wb.createCellStyle();
        noticeCellStyle.setWrapText(true);
        noticeCellStyle.setFont(fontStyle);

        // 文字水平居中
        CellStyle fontCellStyle = wb.createCellStyle();
        fontCellStyle.setAlignment(HorizontalAlignment.CENTER);

        Sheet sheet = wb.createSheet();

        // 设置第1行 合并16格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length));
        // 设置第2行 合并格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 9));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, headers.length));

        //第1行 须知内容
        Row noticeRow = sheet.createRow(0);
        Cell noticeRowCell = noticeRow.createCell(0);
        noticeRowCell.setCellStyle(noticeCellStyle);
        StringBuffer notice = new StringBuffer();
        notice.append("填写须知：").append("\n")
                .append(" <1>不能在本excel表中对房屋信息、进行增加、删除、修改，若房屋信息不足，请进入“企业管理后台-应用管理-资产管理”进行添加，完成后重新下载新模板；").append("\n")
                .append(" <2>红色字段为必填字段，黑色字段为选填字段； ").append("\n")
                .append(" <3>手机号：如果是国内手机号，直接填写手机号即可，如果是国外手机号，需要添加国家码，例如+xx-xxxxxx；").append("\n")
                .append(" <4>地区：省市间用‘-’隔开，且从省开始，例如“广东省-深圳市”；").append("\n")
                .append(" <5>车位信息：请先到企业管理后台-应用管理-资产管理先添加好，再到本excel里填入，多角色用英文的“,”隔开；").append("\n");

        noticeRowCell.setCellValue(notice.toString());
        //自动设置行高
        int colLength = noticeRowCell.getStringCellValue().getBytes().length * 5;
        noticeRow.setHeight((short) (colLength));

        //第2行 员工信息：
        Row firstRow = sheet.createRow(1);
        Cell firstRowCell = firstRow.createCell(0);
        firstRowCell.setCellValue("用户信息");
        firstRowCell.setCellStyle(fontCellStyle);
        firstRowCell = firstRow.createCell(10);
        firstRowCell.setCellValue("房屋信息");
        firstRowCell.setCellStyle(fontCellStyle);

        //第3行 表头
        Row headerRow = sheet.createRow(2);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            if (requrieds[i]) {
                cell.setCellStyle(fontColorCellStyle);
            } else {
                cell.setCellStyle(fontCellStyle);
            }
            cell.setCellValue(headers[i]);
        }

        return sheet;
    }

    /**
     * @param jsonObject
     * @return
     * @author zhouweipeng
     * @throws Exception
     */
    private Page<ResidentHouse> getResidentByParam(JSONObject jsonObject) throws Exception {
        //数据权限类型 1.全部业主 2.我管理的业主 3.下属管理的业主
        String dataPvType = jsonObject.getString("dataPvType");
        List<String> bindTypeList = new ArrayList<>();
        bindTypeList.add(BindType.NO_BIND.getId());
        bindTypeList.add(BindType.BIND.getId());
        FindPage<ResidentHouse> findResidentHouse = jdbcSession.findPage(ResidentHouse.class)
                .eq("houseType",HouseType.PARKING)
                .eq("type", jsonObject.getString("residentType"))
                .in("bindType", bindTypeList)
                .eq(jsonObject,"communityNo")
                .like(jsonObject, "communityName")
                .eq("deleted", false)
                .order("createTime", "desc");
        String search = jsonObject.getString("search");
        if(Objects.nonEmpty(search)){
            findResidentHouse.markBegin();
            findResidentHouse.like("id", search).or();
            findResidentHouse.like("realname", search).or();
            findResidentHouse.like("mobile", search).or();
            findResidentHouse.markEnd();
        }
        //根据不同类型获取业主的数据权限
        if (Objects.nonEmpty(dataPvType)) {
            List<String> assetsIdList = buildAssetsPv(dataPvType);

            if(Objects.isEmpty(assetsIdList)){
                return new Page<>();
            }

            if(Objects.nonEmpty(assetsIdList)){
                findResidentHouse.in("houseNo",assetsIdList);
            }
        }
        return findResidentHouse.page(jsonObject).exe();
    }

    public Map<String, JSONObject> getParkingMap(List<String> ids) throws Exception {

        JSONObject result = doveClient.post("/basic/parking/service/list", http->{
            if(Objects.nonEmpty(ids)){
                JSONObject body = new JSONObject();
                body.put("id",ids);
                http.setBody(body.toString());
            }
        });

        JSONArray data = result.getJSONArray("data");

        if (Objects.isNull(data)) {
            return null;
        }

        Map<String,JSONObject> houseMap = new HashMap<>();
        data.forEach(item->{
            JSONObject obj = (JSONObject) item;
            houseMap.put(obj.getString("id"), obj);
        });

        return houseMap;
    }

    private List<JSONObject> getParkingJsons(Sheet sheet, String[]headers){
        List<JSONObject> result = new ArrayList<>();

        for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
            Row row = sheet.getRow(rIndex);
            if(Objects.isNull(row)){
                continue;
            }
            List<String> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (i < headers.length && Objects.nonNull(cell)) {
                    cell.setCellType(CellType.STRING);
                    String value = cell.getStringCellValue();
                    if(Objects.nonEmpty(value)){
                        cellValues.add(value);
                    }
                }
            }
            if(Objects.isEmpty(cellValues)){
                continue;
            }
            JSONObject parking = new JSONObject();
            parking.put("communityName", ExcelUtil.getCellValue(row.getCell(10)));
            parking.put("yardName", ExcelUtil.getCellValue(row.getCell(11)));
            parking.put("parkingName", ExcelUtil.getCellValue(row.getCell(12)));
            result.add(parking);
        }

        return result;
    }

    private void checkExcelData(Row row, int index, List<String> errMsgs, Map<Integer,Integer> errCount, Map<String, String> houseMap,String[]headers,String residentType) {

        String realname = ExcelUtil.getCellValue(row.getCell(0));
        if (Objects.isEmpty(realname)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【姓名】不能为空");
        }
        String mobile = ExcelUtil.getCellValue(row.getCell(1));
        if (Objects.isEmpty(mobile)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【手机号】不能为空");
        }

        String communityName = ExcelUtil.getCellValue(row.getCell(10));
        if (Objects.isEmpty(communityName)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【项目】不能为空");
        }

        String yardName = ExcelUtil.getCellValue(row.getCell(11));
        if (Objects.isEmpty(yardName)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【车场】不能为空");
        }

        String parkingName = ExcelUtil.getCellValue(row.getCell(12));
        if (Objects.isEmpty(parkingName)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【车位】不能为空");
        }

        String typeDesc = ExcelUtil.getCellValue(row.getCell(13));
        if (Objects.isEmpty(typeDesc)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【身份】不能为空");
        }else{
            if(Objects.isNull(ResidentType.get(typeDesc))){
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：录入的【身份】不匹配");
            }
        }
        if(ResidentType.FRIEND.getId().equals(residentType)){  //家属
            String relasion = ExcelUtil.getCellValue(row.getCell(14));
            if (Objects.isEmpty(relasion)) {
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：【与业主关系】不能为空");
            }
        }
        if(ResidentType.LESSEE.getId().equals(residentType)){  //租客
            String startTime = ExcelUtil.getCellValue(row.getCell(15));
            if (Objects.isEmpty(startTime)) {
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：【租赁开始时间】不能为空");
            }
            String endTime = ExcelUtil.getCellValue(row.getCell(16));
            if (Objects.isEmpty(endTime)) {
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：【租赁结束时间】不能为空");
            }
        }

        StringBuilder builder = new StringBuilder();
        builder.append(communityName).append("-")
                .append(yardName).append("-")
                .append(parkingName);
        String id = houseMap.get(builder.toString());
        if(Objects.isEmpty(id)){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【车位】不存在");
        }
    }

    private JSONObject getExcelData(Row row, Map<String, String> parkingMap,String residentType) {
        String realname = ExcelUtil.getCellValue(row.getCell(0));
        String mobile = ExcelUtil.getCellValue(row.getCell(1));
        String cardNo = ExcelUtil.getCellValue(row.getCell(2));
        String weixinNo = ExcelUtil.getCellValue(row.getCell(3));
        String gender = ExcelUtil.getCellValue(row.getCell(4));
        String birthDay = ExcelUtil.getCellValue(row.getCell(5));
        String email = ExcelUtil.getCellValue(row.getCell(6));
        String address = ExcelUtil.getCellValue(row.getCell(7));
        String offerName = ExcelUtil.getCellValue(row.getCell(8));
        String remark = ExcelUtil.getCellValue(row.getCell(9));
        String communityName = ExcelUtil.getCellValue(row.getCell(10));
        String yardName = ExcelUtil.getCellValue(row.getCell(11));
        String parkingName = ExcelUtil.getCellValue(row.getCell(12));
        String type = ExcelUtil.getCellValue(row.getCell(13));
        String relationType=null;
        String startTime=null;
        String endTime=null;
        if(ResidentType.FRIEND.getId().equals(residentType)){   //家属
            relationType= ExcelUtil.getCellValue(row.getCell(14));
        }else if(ResidentType.LESSEE.getId().equals(residentType)){ //租客
            startTime= ExcelUtil.getCellValue(row.getCell(15));
            endTime =ExcelUtil.getCellValue(row.getCell(16));
        }
        JSONObject result = new JSONObject();
        result.put("realname", realname);
        result.put("mobile", mobile);
        result.put("cardNo", cardNo);
        result.put("cardType", CardType.ID_CARD.getId());
        result.put("weixinNo", weixinNo);
        result.put("gender", gender);
        result.put("birthDay", birthDay);
        result.put("email", email);
        result.put("address", address);
        result.put("offerName", offerName);
        result.put("remark", remark);
        result.put("communityName", communityName);
        result.put("buildName", yardName);
        result.put("houseName", parkingName);
        result.put("houseType", HouseType.PARKING.getId());
        result.put("type", type);
        result.put("relationType", relationType);
        result.put("startTime", startTime);
        result.put("endTime", endTime);

        StringBuilder builder = new StringBuilder();
        builder.append(communityName).append("-")
                .append(yardName).append("-")
                .append(parkingName);
        String id = parkingMap.get(builder.toString());

        result.put("houseNo", id);

        return result;
    }
}
